﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Trade.Model;

namespace Trade.DAL
{
    public class DailyExpenses
    {
        private const string SQL_SELECT_DAILYEXPENSES = "Select D.BillNo, D.Account, D.FeeSubjectNo, S1.SubjectName as FeeSubjectName, D.Amount, D.Department, S2.SubjectName as Dept, D.Employee, D.PayDate, D.EntryDate, D.Remark, D.RecordId from DailyExpenses D left join SubjectMessage S1 on D.FeeSubjectNo = S1.SubjectId left join SubjectMessage S2 on D.Department = S2.SubjectId";
        private const string SQL_WHERE = " where 1 > 2";
        private const string SQL_WHERE_RECORDID = " where D.RecordId = @RecordId";

        public DataTable GetDailyExpensesList()
        {
            return SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_DAILYEXPENSES, null);
        }

        //创建数据表结构
        public DataTable DailyExpensesTemplate()
        {
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_DAILYEXPENSES + SQL_WHERE, null))
            {
                return Utility.ConvertToDataTable(dr);
            }
        }

        //根据指定的记录编号读取相应的日常开支记录
        public DataRow GetDailyExpensesByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            DataTable dt = SQLHelper.ExecuteDataTable(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_DAILYEXPENSES + SQL_WHERE_RECORDID, parm);
            DataRow dr = dt.Rows[0];
            return dr;
        }

        public DailyExpensesInfo GetDailyExpensesInfoByRecordId(int recordId)
        {
            SqlParameter parm = new SqlParameter("@RecordId", SqlDbType.Int);
            parm.Value = recordId;

            DailyExpensesInfo dailyExpensesInfo = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(SQLHelper.connectionString, CommandType.Text, SQL_SELECT_DAILYEXPENSES + SQL_WHERE_RECORDID, parm))
            {
                while (dr.Read())
                {
                    dailyExpensesInfo = new DailyExpensesInfo(dr.GetString(0), dr.GetString(1), dr.GetInt32(2), dr.GetDecimal(3), dr.GetInt32(4), dr.GetString(5), dr.GetDateTime(6), dr.GetDateTime(7),
                                                                dr.GetString(8), dr.GetInt32(9));
                }
            }
            return dailyExpensesInfo;
        }

        //检测日常开支单据编号的唯一性
        public bool IsUniqueBillNo(string billNo, int recordId)
        {
            string sqlSelect = "Select count(BillNo) from DailyExpenses where ltrim(rtrim(BillNo)) = @BillNo and RecordId <> @RecordId";
            SqlParameter[] parms = {
                    new SqlParameter("@BillNo", SqlDbType.VarChar, 30),
                    new SqlParameter("@RecordId", SqlDbType.Int)};

            parms[0].Value = billNo.Trim();
            parms[1].Value = recordId;

            return (int)SQLHelper.ExecuteScalar(SQLHelper.connectionString, CommandType.Text, sqlSelect, parms) == 0 ? true : false;
        }

        public void WriteToDatabase(string action, DailyExpensesInfo dailyExpenses, out int recordId)
        {
            string sql = "Exec Usp_ModifyDailyExpenses @p_Action, @p_BillNo, @p_Account, @p_FeeSubjectNo, @p_Amount, @p_Department, @p_Employee, @p_PayDate, @p_EntryDate, @p_Remark, @p_RecordId out, @p_ErrMsg out, @p_RFlag out";

            SqlParameter[] parms = {
                    new SqlParameter("@p_Action", SqlDbType.Char, 1),
					new SqlParameter("@p_BillNo", SqlDbType.VarChar, 30),
					new SqlParameter("@p_Account", SqlDbType.VarChar, 30),
                    new SqlParameter("@p_FeeSubjectNo", SqlDbType.Int),
                    new SqlParameter("@p_Amount", SqlDbType.Decimal, 15),
                    new SqlParameter("@p_Department", SqlDbType.Int),
                    new SqlParameter("@p_Employee", SqlDbType.VarChar, 20),
                    new SqlParameter("@p_PayDate", SqlDbType.DateTime),
                    new SqlParameter("@p_EntryDate", SqlDbType.DateTime),
                    new SqlParameter("@p_Remark", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RecordId", SqlDbType.Int),
                    new SqlParameter("@p_ErrMsg", SqlDbType.VarChar, 100),
                    new SqlParameter("@p_RFlag", SqlDbType.SmallInt)};

            parms[0].Value = action;
            parms[1].Value = dailyExpenses.BillNo.Trim();
            parms[2].Value = dailyExpenses.Account.Trim();
            parms[3].Value = dailyExpenses.FeeSubjectNo;
            parms[4].Value = dailyExpenses.Amount;
            parms[5].Value = dailyExpenses.Department;
            parms[6].Value = dailyExpenses.Employee.Trim();
            parms[7].Value = dailyExpenses.PayDate;
            parms[8].Value = dailyExpenses.EntryDate;
            parms[9].Value = dailyExpenses.Remark.Trim();
            parms[10].Value = dailyExpenses.RecordId;
            parms[10].Direction = ParameterDirection.InputOutput;
            parms[11].Value = "";
            parms[11].Direction = ParameterDirection.InputOutput;
            parms[12].Value = 0;
            parms[12].Direction = ParameterDirection.InputOutput;

            SQLHelper.ExecuteNonQuery(SQLHelper.connectionString, CommandType.Text, sql, parms);
            recordId = (int)parms[10].Value;
        }
    }
}
